#!/bin/bash

APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table edu.dim_user_zip partition(dt)
select
    id,
    login_name,
    nick_name,
    real_name,
    phone_num,
    email,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    if(rn=2,date_sub('$do_date',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('$do_date',1)) dt
from
(
    select
        id,
        login_name,
        nick_name,
        real_name,
        phone_num,
        email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        start_date,
        end_date,
        row_number() over (partition by id order by start_date desc) rn
    from
    (
        select
            id,
            login_name,
            nick_name,
            real_name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date
        from edu.dim_user_zip
        where dt='9999-12-31'
        union
        select
            id,
            login_name,
            nick_name,
            md5(real_name) real_name,
            md5(phone_num) phone_num,
            md5(email) email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            '$do_date' start_date,
            '9999-12-31' end_date
        from
        (
            select
                data.id,
                data.login_name,
                data.nick_name,
                data.real_name,
                data.phone_num,
                data.email,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                row_number() over (partition by data.id order by ts desc) rn
            from edu.ods_user_info_inc
            where dt='$do_date'
        )t1
        where rn=1
    )t2
)t3;"

dim_province_full="
insert overwrite table edu.dim_province_full partition (dt='2022-07-10')
select
id,
name,
region_id,
area_code,
iso_code,
iso_3166_2
from 
edu.ods_base_province_full
where dt='$do_date';
"
dim_chapter_full="
insert overwrite table edu.dim_chapter_full partition(dt='$do_date')
select
    chapter.id as chapter_id,
    chapter.chapter_name as chapter_name,
    video.id as video_id,
    video.video_name as video_name,
    video.video_status,
    during_sec,
    video_size,
    video.video_source_id,

    chapter.course_id,
    course_name,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name,
    chapter.is_free,
    chapter.create_time,
    chapter.update_time
from (
    select *
    from edu.ods_chapter_info_full
    where dt = '$do_date'
) chapter
left join (
    select *
    from edu.ods_video_info_full
    where dt = '$do_date'
) video
on chapter.video_id = video.id
left join (
    select *
    from edu.ods_base_source_full
    where dt = '$do_date'
) source
on video.video_source_id = source.id
left join (
    select *
    from edu.ods_course_info_full
    where dt = '$do_date'
) course
on chapter.course_id = course.id
left join (
    select * 
    from edu.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from edu.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"

dim_test_full="
insert overwrite table edu.dim_test_full partition(dt='$do_date')
select 
    paper_question.id as id,
    paper_question.question_id,
    paper_question.score,
    paper_question.create_time,
    paper.id as paper_id,
    paper.paper_title,
    paper.course_id,
    course.course_name,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name
from (
    select *
    from edu.ods_test_paper_question_full
    where dt = '$do_date'
) paper_question
left join (
    select *
    from edu.ods_test_paper_full
    where dt = '$do_date'
) paper
on paper_question.paper_id = paper.id
left join (
    select * 
    from edu.ods_course_info_full
    where dt = '$do_date'
) course
on paper.course_id = course.id
left join (
    select *
    from edu.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from edu.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"
dim_course_full="
with t1 as (
    select id,
           course_name,
           course_slogan,
           course_cover_url,
           subject_id,
           teacher,
           publisher_id,
           chapter_num,
           origin_price,
           reduce_amount,
           actual_price,
           course_introduce,
           create_time,
           update_time,
           deleted
    from ${APP}.ods_course_info_full
    where dt = '$do_date'
),
     t2 as (
         select id,
                subject_name,
                category_id
         from ${APP}.ods_base_subject_info_full
         where dt = '$do_date'
     ), t3 as (
         select id,
                category_name
         from ${APP}.ods_base_category_info_full
         where dt = '$do_date'
     )
insert overwrite table ${APP}.dim_course_full partition (dt = '$do_date')
select t1.id,
       t1.course_name,
       t3.id category_id,
       t3.category_name,
       t1.course_slogan,
       t1.course_cover_url,
       t1.subject_id,
       t2.subject_name,
       t1.teacher,
       t1.publisher_id,
       t1.chapter_num,
       t1.origin_price,
       t1.reduce_amount,
       t1.actual_price,
       t1.course_introduce,
       t1.create_time,
       t1.update_time,
       t1.deleted
from t1
left join t2 on t1.subject_id=t2.id
left join t3 on t2.category_id=t3.id;
"
dim_paper_full="
with t1 as (
    select id,
           paper_title,
           course_id,
           create_time,
           update_time,
           publisher_id,
           deleted
    from ${APP}.ods_test_paper_full
    where dt = '$do_date'
    ),
     t2 as (
         select id,
                course_name
         from ${APP}.ods_course_info_full
         where dt = '$do_date'
    ),
     t3 as (
         select data.id,
                data.exam_id,
                data.paper_id,
                data.question_id
         from ${APP}.ods_test_exam_question_inc
         where dt = '$do_date'
               )
insert overwrite table ${APP}.dim_paper_full partition (dt = '$do_date')
select t1.id,
       t1.paper_title,
       t1.course_id,
       t2.course_name,
       t3.exam_id,
       t3.question_id,
       t1.create_time,
       t1.update_time,
       t1.publisher_id,
       t1.deleted
from t1
         left join t2 on t1.course_id = t2.id
         left join t3 on t1.id = t3.paper_id;
"



case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_test_full")
    hive -e "$dim_test_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_paper_full")
    hive -e "$dim_paper_full"
;;
"all")
    hive -e "$dim_user_zip$dim_province_full$dim_chapter_full$dim_test_full$dim_course_full$dim_paper_full"
;;
esac
